package com.example.gdeserver.mapper;

import com.example.gdeserver.bean.DataEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface DataMapper {
    @Select("${sqlstatement}")
    public List<Map<String, Object>> sql(@Param("sqlstatement") String sqlstatement);

    //   公司列表
    @Select("select count(d.c) ,d.c from d where d.c  is not null and d.c <> '.'and d.c <> 'null'and d.c <> 'NULL'  and  length(d.c) >1 group by d.c order by count(d.c) desc;")
    public List<DataEntity> getCompanys();

    //    公司下面的程序
//    @Select("select count(d.p) as count ,d.p from d  where d.c =  #{compname} group by p order by count(p) desc")
    @Select("select count(d.p) numbers, concat(d.n,'(v',REPLACE(REPLACE(d.v, ',', '.'),' ',''),')') as pname ,p from d  where d.c = #{compname}  group by   p ,n,v order by count(p) desc;")
    public List<Map<String, Object>> getProgramUsageNumberByCompany(String compname);


    //    程序使用的地理位置
    @Select("select distinct city from d left join u on u.userid = d.id and d.p in (select p from d where p = #{processname}) and city is not null and city <> 'null'")
    public List<Map<String, Object>> getSoftwareUsagePlace(String processname);

    //    使用者性别
    @Select("select count(gender),gender from d left join u on d.id =u.userid and p=#{processname}    group by u.gender  order by count(gender) desc;")
    public List<Map<String, Object>> getSoftwareUsageGender(String processname);

    //    使用农村城市占比
    @Select("select count(iscity),iscity from d left join u on d.id =u.userid and p=#{processname}  group by u.iscity  order by count(iscity) desc;")
    public List<Map<String, Object>> getSoftwareUsageCity(String processname);

    //    使用者出生年月
    @Select("select count(birthday),birthday from d left join u on d.id =u.userid and p=#{processname}   group by u.birthday  order by count(birthday) desc;")
    public List<Map<String, Object>> getSoftwareUsageBirthday(String processname);

    //    使用者文化程度
    @Select("select count(edu),edu from d left join u on d.id =u.userid and  p=#{processname}  group by u.edu  order by count(edu) desc;")
    public List<Map<String, Object>> getSoftwareUsageEdu(String processname);

    //     使用者工作
    @Select("select count(job),job from d left join u on d.id =u.userid and p=#{processname} group by u.job  order by count(job) desc;")
    public List<Map<String, Object>> getSoftwareUsageJob(String processname);

    //     使用者收入
    @Select("select count(income),income from d left join u on d.id =u.userid and p=#{processname} group by u.income  order by count(income) desc;")
    public List<Map<String, Object>> getSoftwareUsageIncome(String processname);

    //    所有软件的使用次数
    @Select("select p,count(p) from d  group by p order by  count(p) desc  limit  100;")
    public List<Map<String, Object>> allSoftwareUsage( );

    //    所有软件的使用次数
    @Select("select u,count(u) from d where u <> 'NULL' and u <> 'null' group by u order by  count(u) desc limit 100;")
    public List<Map<String, Object>> allWebsitsUsage( );




}
